import java.sql.*;
import java.util.Scanner;

public class BookManagementSystem {
    // MySQL数据库连接URL、用户名和密码
    private static final String URL = "jdbc:mysql://localhost:3306/book_management";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            // 使用Scanner进行命令行交互
            Scanner scanner = new Scanner(System.in);
            while (true) {
                // 显示主菜单
                System.out.println("书籍信息管理系统");
                System.out.println("1.增加书籍信息");
                System.out.println("2.显示书籍信息");
                System.out.println("3.修改书籍信息");
                System.out.println("4.删除书籍信息");
                System.out.println("5.按照出版时间排序");
                System.out.println("0.退出系统");
                System.out.print("请输入选择：");

                // 获取用户选择
                int choice = scanner.nextInt();
                scanner.nextLine();  // 消耗换行符

                // 根据用户选择执行相应操作
                switch (choice) {
                    case 1:
                        addBook(conn, scanner);
                        break;
                    case 2:
                        displayBooks(conn);
                        break;
                    case 3:
                        updateBook(conn, scanner);
                        break;
                    case 4:
                        deleteBook(conn, scanner);
                        break;
                    case 5:
                        displayBooksSortedByDate(conn);
                        break;
                    case 0:
                        System.out.println("退出系统。");
                        return;
                    default:
                        System.out.println("无效选择，请重新输入。");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 添加书籍信息
    private static void addBook(Connection conn, Scanner scanner) throws SQLException {
        // 提示用户输入书籍信息
        System.out.print("输入书名：");
        String title = scanner.nextLine();
        System.out.print("输入作者：");
        String author = scanner.nextLine();
        System.out.print("输入出版社：");
        String publisher = scanner.nextLine();
        System.out.print("输入出版时间 (YYYY-MM-DD)：");
        String publishDate = scanner.nextLine();
        System.out.print("输入ISBN：");
        String isbn = scanner.nextLine();
        System.out.print("输入价格：");
        double price = scanner.nextDouble();
        System.out.print("输入数量：");
        int quantity = scanner.nextInt();
        scanner.nextLine();  // 消耗换行符

        // 插入书籍信息的SQL语句
        String sql = "INSERT INTO books (title, author, publisher, publish_date, isbn, price, quantity) VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, title);
            pstmt.setString(2, author);
            pstmt.setString(3, publisher);
            pstmt.setDate(4, Date.valueOf(publishDate));
            pstmt.setString(5, isbn);
            pstmt.setDouble(6, price);
            pstmt.setInt(7, quantity);
            pstmt.executeUpdate();
            System.out.println("书籍信息已添加。");
        }
    }

    // 显示所有书籍信息
    private static void displayBooks(Connection conn) throws SQLException {
        // 查询所有书籍信息的SQL语句
        String sql = "SELECT * FROM books";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                // 输出每本书的信息
                System.out.println("ID: " + rs.getInt("id"));
                System.out.println("书名: " + rs.getString("title"));
                System.out.println("作者: " + rs.getString("author"));
                System.out.println("出版社: " + rs.getString("publisher"));
                System.out.println("出版时间: " + rs.getDate("publish_date"));
                System.out.println("ISBN: " + rs.getString("isbn"));
                System.out.println("价格: " + rs.getDouble("price"));
                System.out.println("数量: " + rs.getInt("quantity"));
                System.out.println("-------------");
            }
        }
    }

    // 更新书籍信息
    private static void updateBook(Connection conn, Scanner scanner) throws SQLException {
        // 提示用户输入要修改的书籍ID
        System.out.print("输入要修改的书籍ID：");
        int id = scanner.nextInt();
        scanner.nextLine();  // 消耗换行符
        // 提示用户输入新的书籍信息
        System.out.print("输入新的书名：");
        String title = scanner.nextLine();
        System.out.print("输入新的作者：");
        String author = scanner.nextLine();
        System.out.print("输入新的出版社：");
        String publisher = scanner.nextLine();
        System.out.print("输入新的出版时间 (YYYY-MM-DD)：");
        String publishDate = scanner.nextLine();
        System.out.print("输入新的ISBN：");
        String isbn = scanner.nextLine();
        System.out.print("输入新的价格：");
        double price = scanner.nextDouble();
        System.out.print("输入新的数量：");
        int quantity = scanner.nextInt();
        scanner.nextLine();  // 消耗换行符

        // 更新书籍信息的SQL语句
        String sql = "UPDATE books SET title = ?, author = ?, publisher = ?, publish_date = ?, isbn = ?, price = ?, quantity = ? WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, title);
            pstmt.setString(2, author);
            pstmt.setString(3, publisher);
            pstmt.setDate(4, Date.valueOf(publishDate));
            pstmt.setString(5, isbn);
            pstmt.setDouble(6, price);
            pstmt.setInt(7, quantity);
            pstmt.setInt(8, id);
            pstmt.executeUpdate();
            System.out.println("书籍信息已更新。");
        }
    }

    // 删除书籍信息
    private static void deleteBook(Connection conn, Scanner scanner) throws SQLException {
        // 提示用户输入要删除的书籍ID
        System.out.print("输入要删除的书籍ID：");
        int id = scanner.nextInt();
        scanner.nextLine();  // 消耗换行符

        // 删除书籍信息的SQL语句
        String sql = "DELETE FROM books WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            System.out.println("书籍信息已删除。");
        }
    }

    // 按出版时间排序显示书籍信息
    private static void displayBooksSortedByDate(Connection conn) throws SQLException {
        // 查询按出版时间排序的书籍信息的SQL语句
        String sql = "SELECT * FROM books ORDER BY publish_date";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                // 输出每本书的信息
                System.out.println("ID: " + rs.getInt("id"));
                System.out.println("书名: " + rs.getString("title"));
                System.out.println("作者: " + rs.getString("author"));
                System.out.println("出版社: " + rs.getString("publisher"));
                System.out.println("出版时间: " + rs.getDate("publish_date"));
                System.out.println("ISBN: " + rs.getString("isbn"));
                System.out.println("价格: " + rs.getDouble("price"));
                System.out.println("数量: " + rs.getInt("quantity"));
                System.out.println("-------------");
            }
        }
    }
}
